import sqlite3
import requests
from bs4 import BeautifulSoup

urls=[
"https://www.qikanchina.com/thesis/detail/5034600",
    "https://www.qikanchina.com/thesis/detail/4833854"

]

for url in urls:

    # 示例HTML字符串，实际应用中应从网页中获取
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36'
    }
    html_doc = requests.get(url,headers=headers)


    # 使用BeautifulSoup解析HTML
    soup = BeautifulSoup(html_doc.text, 'html.parser')

    # 连接到SQLite数据库
    # 如果数据库不存在，会自动在当前目录创建
    conn = sqlite3.connect('thesis_database.db')
    c = conn.cursor()

    # 创建表
    c.execute('''CREATE TABLE IF NOT EXISTS theses  
                 (title TEXT, abstract TEXT, authors TEXT)''')

    # 提取数据
    title = soup.find('h1', class_=None).text.strip()
    abstract = soup.find('span', class_='abstract').text.strip()
    authors = [a.text.strip() for a in soup.find('div', class_='author').find_all('a')]
    authors_str = ', '.join(authors)

    # 插入数据
    c.execute("INSERT INTO theses (title, abstract, authors) VALUES (?, ?, ?)",
              (title, abstract, authors_str))

    # 提交事务
    conn.commit()

    # 关闭连接
    conn.close()

    # 查询并打印数据以验证
    conn = sqlite3.connect('thesis_database.db')
    c = conn.cursor()
    c.execute("SELECT * FROM theses")

    for row in c.fetchall():
        print(row)

    conn.close()